<?php
session_start();

if ( !$_SESSION['customer']['id'] )
{
    header('Location: login.php');
    exit();
}
else
{
    require_once('core/core.php');

    require_once('inc/config.db.php');
//    require_once('inc/config.smarty.php');

    $invoice = new Invoice;
    $smarty  = $invoice->initSmarty();

    switch ( $_GET['action'] )
    {
	case	'logout':
	    session_destroy();
	    header('Location: index.php');
	    break;

	default:
	    $smarty->assign('customer',$_SESSION['customer']);

// Список договоров
	    if ( $result = mysql_query('SELECT `ct`.*,`e`.`executive_name` FROM `contract` ct JOIN `executive` e ON `e`.`id`=`ct`.`executive_id` WHERE `account_id`='.$_SESSION['customer']['account_id']) )
	    {
		$data = array();

		while ( $row = mysql_fetch_assoc($result) )
		{
		    $data[] = $row;
		}

		$smarty->assign('contracts',$data);
	    }

	    $result = mysql_query('SELECT `manager_name` FROM `manager` WHERE `id`='.$_SESSION['customer']['manager_id']);
	    if ( mysql_affected_rows()>0 )
	    {
		$smarty->assign('manager',mysql_fetch_assoc($result));
	    }

	    $result = mysql_query('SELECT * FROM `contact` WHERE `account_id`='.$_SESSION['customer']['account_id']);
	    if ( mysql_affected_rows()>0 )
	    {
		while( $row = mysql_fetch_assoc($result) )
		{
		    $contact[] = $row;
		}
		$smarty->assign('contacts',$contact);
	    }

// Расчётные документы - группировка по исполнителям
	    $query = sprintf
	    (
		'SELECT `invoice`.`id` AS `id`,`date`,`account`,`act`,`invoice`,`detail`,`contract`.`executive_id`,`executive`.`executive_name`'
		.' FROM `invoice`'
		.' JOIN `contract` ON `contract`.`id`=`invoice`.`contract_id`'
		.' JOIN `executive` ON `executive`.`id`=`contract`.`executive_id`'
		.' WHERE `contract`.`account_id`=%d',
		$_SESSION['customer']['account_id']
	    );

	    if ( $result = $invoice->_doQuery($query) )
	    {
		while ( $row = mysql_fetch_assoc($result) )
		{
		    $docs[$row['executive_id']][] = $row;
		}

		$smarty->assign('docs',$docs);
	    }
	    else { die(mysql_error()); }

// Услуги - группировка по исполнителям
	    $query = sprintf
	    (
		'SELECT '
		    .'`s`.*,'
		    .'`e`.`executive_name`,'
		    .'`sn`.`service_name`,'
		    .'`pr`.`rate_name`,'
		    .'`spn`.`phone_number`'
		.' FROM `service` s'
		.' JOIN `service_name` sn ON `sn`.`id`=`s`.`service_name_id`'
		.' JOIN `executive` e ON `e`.`id`=`s`.`executive_id`'
		.' LEFT JOIN `service_phone_number` spn ON `spn`.`service_id`=`s`.`id`'
		.' LEFT JOIN `phone_rate` pr ON `pr`.`id`=`spn`.`phone_rate_id`'
		.' WHERE `s`.`account_id`=%d'
		.' ORDER BY `s`.`start_date`,`s`.`stop_date`,`s`.`id`',
		$_SESSION['customer']['account_id']
	    );

	    if ( $result = mysql_query($query) )
	    {
		while ( $row = mysql_fetch_assoc($result) )
		{
		    $query = sprintf
		    (
			'SELECT'
			    .' CONCAT(SUBSTRING_INDEX(sum(det.cost),\'.\',1),\',\',SUBSTRING_INDEX(sum(det.cost),\'.\',-1)) as cost'
			.' FROM detalisation det'
			.' JOIN service_phone_number spn ON det.service_phone_number_id = spn.id'
			.' JOIN service ser ON ser.id = spn.service_id'
			.' JOIN phone_direction pd ON det.phone_direction_id = pd.id'
			.' WHERE ser.id=%d'
			    .' AND date BETWEEN DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%%Y-%%m-01\') AND DATE_SUB(DATE_FORMAT(NOW(),\'%%Y-%%m-01\'), INTERVAL 1 SECOND)', // CONCAT(YEAR(Now()-INTERVAL 1 MONTH),'-',MONTH(Now()-INTERVAL 1 MONTH),'-',1) and LAST_DAY(Now()-INTERVAL 1 MONTH)+INTERVAL 1 DAY;'
			$row['id']
		    ); //die($query);

		    if ( $last_cost = mysql_fetch_array(mysql_query($query)) ) { $row['last_cost'] = $last_cost[0]; }

		    $service[$row['executive_id']][] = $row;
		}

		$smarty->assign('services',$service);
	    }

// Обычные документы
	    $result = mysql_query('SELECT * FROM `document` WHERE `type`=\'ordinary\' AND `account_id`='.$_SESSION['customer']['account_id']);
	    if ( mysql_affected_rows()>0 )
	    {
		while( $row = mysql_fetch_assoc($result) )
		{
		    if ( !file_exists('/var/www/region.ftn.org.ru/files/'.$row['path']) ) { continue; }
		    $doc_ord[] = $row;
		}
		$smarty->assign('docs_regular',$doc_ord);
	    }

/*
	    $result = mysql_query('SELECT * FROM `document` WHERE `type`=\'ttk\' AND `account_id`='.$_SESSION['customer']['account_id']);
	    if ( mysql_affected_rows()>0 )
	    {
		while( $row = mysql_fetch_assoc($result) )
		{
		    if ( !file_exists($row['path']) ) { continue; }
		    $doc_ttk[] = $row;
		}
		$smarty->assign('docs_ttk',$doc_ttk);
	    }
*/
	    $smarty->display('customer/customer.html');
	    break;

/*
    Блок детализации звонков
*/
	case	'detail':
	    if ( count($_GET['service_id'])==0 ) { die(json_encode(array())); }

// Подсчёт общего количества записей
	    $query = sprintf
	    (
		'SELECT COUNT(*)'
		.' FROM detalisation det'
		.' JOIN service_phone_number spn ON det.service_phone_number_id = spn.id'
		.' JOIN service ser ON ser.id = spn.service_id'
		.' WHERE ser.id IN(%s)'
		    .' AND date BETWEEN %s AND %s'
		    .' AND `ser`.`account_id`=%d',
		implode(',',$_GET['service_id']),
		($_GET['date_start']?sprintf('\'%s\'',$_GET['date_start']):'DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%Y-%m-01\')'),
		($_GET['date_stop']?sprintf('\'%s\' + INTERVAL 1 DAY',$_GET['date_stop']):'LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'),
		$_SESSION['customer']['account_id']
	    );

	    if ( $result = mysql_query($query) ) { $count = mysql_fetch_array($result); } else { die(mysql_error()); }

	    if ( $_GET['csv'] == 'yes' )
	    {
		header('Content-Type: text/plain; charset=utf-8'); 
		header('Content-Disposition: attachment; filename=detail.csv'); 

		foreach ( $_GET['service_id'] as $id )
		{
		    $query = sprintf
		    (
			'SELECT '
//			    .'det.id,'
			    .'spn.phone_number,'
			    .'det.dst,'
			    .'det.duration,'
			    .'CONCAT(SUBSTRING_INDEX(det.cost,\'.\',1),\',\',SUBSTRING_INDEX(det.cost,\'.\',-1)) as cost,'
			    .'det.date,'
			    .'det.cost AS `cost_flat`,'
			    .'pd.title'
			.' FROM detalisation det'
			.' JOIN service_phone_number spn ON det.service_phone_number_id = spn.id'
			.' JOIN service ser ON ser.id = spn.service_id'
			.' JOIN phone_direction pd ON det.phone_direction_id = pd.id'
			.' WHERE ser.id IN(%s)'
			    .' AND date BETWEEN %s AND %s'
			    .' AND `ser`.`account_id`=%d'
			.' ORDER BY `det`.`date`',
			$id,
			($_GET['date_start']?sprintf('\'%s\'',$_GET['date_start']):'DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%Y-%m-01\')'),
			($_GET['date_stop']?sprintf('\'%s\' + INTERVAL 1 DAY',$_GET['date_stop']):'LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'),
			$_SESSION['customer']['account_id']
		    );

		    if ( $result = mysql_query($query) )
		    {
			$total = array();

			while ( $row = mysql_fetch_row($result) )
			{
			    $row[2] = ($row[2]%60)>5 ? ceil($row[2]/60) : floor($row[2]/60);

			    $total['number']	= $row[0];
			    $total['duration']	+= $row[2];
			    $total['sum']	+= $row[5];
			    $total['count']++;

			    unset($row[5]);

			    if ( $_GET['in_win'] == 'yes' )
			    {
				printf("%s\n",iconv('utf-8','windows-1251',implode(';',$row)));
			    }
			    else
			    {
				printf("%s\n",implode(';',$row));
			    }
			}

			echo ";;\n";

			if ( $_GET['in_win'] == 'yes' )
			{
			    printf
			    (
				"%s\n",
				iconv
				(
				    'utf-8',
				    'windows-1251',
				    implode
				    (
					' ',
					array
					(
					    sprintf('ИТОГО ПО НОМЕРУ %s',$total['number']),
					    sprintf('ВЫЗОВОВ %d',$total['count']),
					    sprintf('ПРОДОЛЖИТЕЛЬНОСТЬ %d',$total['duration']),
					    sprintf('НА СУММУ %0.2f',$total['sum']),
					    NULL,
					    NULL
					)
				    )
				)
			    );
			}
			else
			{
			    printf
			    (
				"%s\n",
				implode
				(
				    ' ',
				    array
				    (
					sprintf('ИТОГО ПО НОМЕРУ %s',$total['number']),
					sprintf('ВЫЗОВОВ %d',$total['count']),
					sprintf('ПРОДОЛЖИТЕЛЬНОСТЬ %d',$total['duration']),
					sprintf('НА СУММУ %0.2f',$total['sum']),
					NULL,
					NULL
				    )
				)
			    );
			}

			echo "########################################################################;\n";
		    }
		}

		exit();
	    }

	    $query = sprintf
	    (
		'SELECT '
		    .'det.id,'
		    .'spn.phone_number,'
		    .'det.dst,'
		    .'SEC_TO_TIME(det.duration),'
		    .'CONCAT(SUBSTRING_INDEX(det.cost,\'.\',1),\',\',SUBSTRING_INDEX(det.cost,\'.\',-1)) as cost,'
		    .'det.date,'
		    .'pd.title'
		.' FROM detalisation det'
		.' JOIN service_phone_number spn ON det.service_phone_number_id = spn.id'
		.' JOIN service ser ON ser.id = spn.service_id'
		.' JOIN phone_direction pd ON det.phone_direction_id = pd.id'
		.' WHERE ser.id IN(%s)'
		    .' AND date BETWEEN %s AND %s'
		    .' AND `ser`.`account_id`=%d'
		.' ORDER BY `det`.`date`'
		.' LIMIT %d,%d',
		implode(',',$_GET['service_id']),
		($_GET['date_start']?sprintf('\'%s\'',$_GET['date_start']):'DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH),\'%Y-%m-01\')'),
		($_GET['date_stop']?sprintf('\'%s\' + INTERVAL 1 DAY',$_GET['date_stop']):'LAST_DAY(DATE_SUB(NOW(), INTERVAL 1 MONTH))'),
		$_SESSION['customer']['account_id'],
		$_GET['rows']*($_GET['page']-1),
		$_GET['rows']
	    );

	    if ( $result=mysql_query($query) )
	    {
		while ( $row = mysql_fetch_assoc($result) )
		{
		    $data[] = array('id'=>$row['id'],'cell'=>array_values($row));
		}

//		die($query);

		echo json_encode
		(
		    array
		    (
			'records'	=> $count[0],
			'total'	=> ceil($count[0]/$_GET['rows']),
			'page'	=> $_GET['page'],
			'rows'	=> $data
		    )
		);
	    } else { die($query); }
	    exit();
	    break;
    }
}
?>